﻿Imports DTO
Imports System.Data.OleDb
Public Class GiaoVienDAO
    Inherits Connect

    Public Sub XoaPC(ByVal dto As GiaoVienDTO)
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim sql As String = "Delete from PhuTrach where MagiaoVien='" & dto.MaGiaoVien & "'"
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub

    Public Sub XoaRBGV(ByVal dto As GiaoVienDTO)
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim sql As String = "Delete from RangBuocGiaoVien where MagiaoVien='" & dto.MaGiaoVien & "'"
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub

    Public Sub XoaGV(ByVal dto As GiaoVienDTO)
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim sql As String = "Delete from GiaoVien where MagiaoVien='" & dto.MaGiaoVien & "'"
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub

#Region "Lấy lịch rảnh"
    Function TietHoc() As List(Of String)
        Dim list As New List(Of String)
        Dim sql As String = "select tiethoc from RangBuocGiaoVien group by tiethoc"
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            list.Add(dr.GetValue(0).ToString)
        End While
        cn.Close()
        Return list
    End Function

    Function Thu2(ByVal dto As GiaoVienDTO) As List(Of String)
        Dim list As New List(Of String)
        Dim sql As String = "SELECT trangthai " & _
       "FROM RangBuocGiaoVien " & _
"WHERE MaGiaoVien='" & dto.MaGiaoVien & "' AND thu=2"

        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            list.Add(dr.GetValue(0).ToString)
        End While
        cn.Close()
        Return list
    End Function

    Function Thu3(ByVal dto As GiaoVienDTO) As List(Of String)
        Dim list As New List(Of String)
        Dim sql As String = "SELECT trangthai " & _
       "FROM RangBuocGiaoVien " & _
"WHERE MaGiaoVien='" & dto.MaGiaoVien & "' AND thu=3"

        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            list.Add(dr.GetValue(0).ToString)
        End While
        cn.Close()
        Return list
    End Function

    Function Thu4(ByVal dto As GiaoVienDTO) As List(Of String)
        Dim list As New List(Of String)
        Dim sql As String = "SELECT trangthai " & _
       "FROM RangBuocGiaoVien " & _
"WHERE MaGiaoVien='" & dto.MaGiaoVien & "' AND thu=4"

        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            list.Add(dr.GetValue(0).ToString)
        End While
        cn.Close()
        Return list
    End Function

    Function Thu5(ByVal dto As GiaoVienDTO) As List(Of String)
        Dim list As New List(Of String)
        Dim sql As String = "SELECT trangthai " & _
       "FROM RangBuocGiaoVien " & _
"WHERE MaGiaoVien='" & dto.MaGiaoVien & "' AND thu=5"

        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            list.Add(dr.GetValue(0).ToString)
        End While
        cn.Close()
        Return list
    End Function

    Function Thu6(ByVal dto As GiaoVienDTO) As List(Of String)
        Dim list As New List(Of String)
        Dim sql As String = "SELECT trangthai " & _
       "FROM RangBuocGiaoVien " & _
"WHERE MaGiaoVien='" & dto.MaGiaoVien & "' AND thu=6"

        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            list.Add(dr.GetValue(0).ToString)
        End While
        cn.Close()
        Return list
    End Function

    Function Thu7(ByVal dto As GiaoVienDTO) As List(Of String)
        Dim list As New List(Of String)
        Dim sql As String = "SELECT trangthai " & _
       "FROM RangBuocGiaoVien " & _
"WHERE MaGiaoVien='" & dto.MaGiaoVien & "' AND thu=7"

        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            list.Add(dr.GetValue(0).ToString)
        End While
        cn.Close()
        Return list
    End Function

    Function Thu8(ByVal dto As GiaoVienDTO) As List(Of String)
        Dim list As New List(Of String)
        Dim sql As String = "SELECT trangthai " & _
       "FROM RangBuocGiaoVien " & _
"WHERE MaGiaoVien='" & dto.MaGiaoVien & "' AND thu=8"

        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            list.Add(dr.GetValue(0).ToString)
        End While
        cn.Close()
        Return list
    End Function

    Public Function LichGV(ByVal dto As GiaoVienDTO) As DataTable
        Dim dt As New DataTable
        dt.Columns.Add("TietHoc", GetType(Byte))
        dt.Columns.Add("thu2", GetType(String))
        dt.Columns.Add("thu3", GetType(String))
        dt.Columns.Add("thu4", GetType(String))
        dt.Columns.Add("thu5", GetType(String))
        dt.Columns.Add("thu6", GetType(String))
        dt.Columns.Add("thu7", GetType(String))
        dt.Columns.Add("thu8", GetType(String))

        Dim tiet As New List(Of String)
        tiet = TietHoc()
        Dim arrthu2 As New List(Of String)
        Dim arrthu3 As New List(Of String)
        Dim arrthu4 As New List(Of String)
        Dim arrthu5 As New List(Of String)
        Dim arrthu6 As New List(Of String)
        Dim arrthu7 As New List(Of String)
        Dim arrthu8 As New List(Of String)

        arrthu2 = Thu2(dto)
        arrthu3 = Thu3(dto)
        arrthu4 = Thu4(dto)
        arrthu5 = Thu5(dto)
        arrthu6 = Thu6(dto)
        arrthu7 = Thu7(dto)
        arrthu8 = Thu8(dto)
        For i As Integer = 0 To tiet.Count - 1
            dt.Rows.Add()
            dt.Rows(i).Item(0) = tiet(i).ToString
            dt.Rows(i).Item(1) = arrthu2(i).ToString
            dt.Rows(i).Item(2) = arrthu3(i).ToString
            dt.Rows(i).Item(3) = arrthu4(i).ToString
            dt.Rows(i).Item(4) = arrthu5(i).ToString
            dt.Rows(i).Item(5) = arrthu6(i).ToString
            dt.Rows(i).Item(6) = arrthu7(i).ToString
            dt.Rows(i).Item(7) = arrthu8(i).ToString
        Next
        Return dt
    End Function
#End Region

    Public Function DSMonHocCuaGV(ByVal dto As GiaoVienDTO) As List(Of String)
        Dim ds As New List(Of String)
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim sql As String
        sql = "SELECT tenmonhoc " & _
        "FROM MonHoc INNER JOIN PhuTrach ON MonHoc.MaMonHoc = PhuTrach.MaMonHoc " & _
        "where phutrach.magiaovien='" & dto.MaGiaoVien & "'"
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read()
            ds.Add(dr.GetString(0))
        End While
        cn.Close()
        Return ds
    End Function

    Public Function LayDSGiaoVien() As DataTable
        Dim dt As New DataTable
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData()
        Dim sql As String = "select HoTengiaoVien, TenTat from giaovien"
        Dim da As New OleDbDataAdapter(sql, cn)
        da.Fill(dt)
        dt.Columns.Add("STT", GetType(Integer))
        For i As Integer = 0 To dt.Rows.Count - 1
            dt.Rows(i).Item("STT") = i + 1
        Next
        cn.Close()
        cn.Close()
        Return dt
    End Function

    Public Function LayMaGiaoVien(ByVal dto As GiaoVienDTO) As String
        Dim ma As String
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim sql As String
        sql = "select MaGiaoVien from giaovien where " & _
        "HoTengiaovien='" & dto.HoTenGiaoVien & "' and " & _
        "Tentat='" & dto.TenTat & "'"
        Dim cmd As New OleDbCommand(sql, cn)
        ma = cmd.ExecuteScalar.ToString
        cn.Close()
        Return ma
    End Function

    Public Function LayDCGiaoVien(ByVal dto As GiaoVienDTO) As String
        Dim dc As String
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim sql As String
        sql = "select DiaChi from giaovien where " & _
        "HoTengiaovien='" & dto.HoTenGiaoVien & "' and " & _
        "Tentat='" & dto.TenTat & "'"
        Dim cmd As New OleDbCommand(sql, cn)
        dc = cmd.ExecuteScalar.ToString
        cn.Close()
        Return dc
    End Function

    Public Function LaySDTGiaoVien(ByVal dto As GiaoVienDTO) As String
        Dim sdt As String
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim sql As String
        sql = "select DienThoai from giaovien where " & _
        "HoTengiaovien='" & dto.HoTenGiaoVien & "' and " & _
        "Tentat='" & dto.TenTat & "'"
        Dim cmd As New OleDbCommand(sql, cn)
        sdt = cmd.ExecuteScalar.ToString
        cn.Close()
        Return sdt
    End Function

    Public Function TaoMaGV() As String
        Dim magv As String
        magv = LayMa("GiaoVien", "MaGiaoVien")
        Return magv
    End Function
End Class
